using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Reflection;
using System.Text;

namespace MushManager.DAL
{

	public abstract class DALEngine : IDisposable, IDALEngine
	{		
		// 
		// private data members
		//
		IDbConnection conn       = null;
		string connectionString  = "";
		ArrayList parameters = new ArrayList();
		bool canClose = true;
		
		
		public DALEngine(string connectionString)
		{
			this.connectionString = connectionString;
		}
		
		public bool CanClose
		{
			get { return canClose;  }
			set { canClose = value;  }
		}
		
		public string ConnectionString
		{
			get { return connectionString;  }
			set { connectionString = value; }
		}
		
		protected IDbConnection Connection
		{
			get 
			{ 
				if (conn == null)
					conn = GetConnection();
					
				return conn;  
			}
		}
		
		protected ArrayList Parameters
		{
			get { return parameters; }
		}
		
		protected abstract IDbConnection GetConnection();
		protected abstract IDbCommand CreateCommand(string spName);
		
		public void Close()
		{		
			if (CanClose && conn != null)
			{
				conn.Dispose();
				conn = null;			
			}
		}		
	
		
		public void Dispose()
		{
			Close();
		
			GC.SuppressFinalize(this);
		}
	
			
		public DALParameter GetParameter(string name)
		{
			foreach (DALParameter param in Parameters)
			{
				if (param.Name == name)
					return param;
			}
			
			return null;
		}
		
		void UpdateOutputParameters(IDbCommand cmd)
		{
			int index = 0;
			foreach (DALParameter param in Parameters)
			{
				if (param.Direction == ParameterDirection.Output || param.Direction == ParameterDirection.InputOutput)
				{
					param.Value = ((IDataParameter)cmd.Parameters[index]).Value;
				
				}
				
				index++;
			}			
		
		}
		
		public void AddParameter(DALParameter param)
		{
			Parameters.Add(param);
		}
		
		public void ClearParameters()
		{
			Parameters.Clear();
		}

		public abstract void ExecSP_DataSet(string spName, DataSet dataSet, string tableName);

	
		public IDataReader ExecSP_DataReader(string spName)
		{
			return ExecSP_DataReader(spName, CommandBehavior.Default);
		}
		
		public IDataReader ExecSP_DataReader(string spName, CommandBehavior behavior)
		{
			IDataReader result = null;
			
			try
			{
				IDbCommand cmd = CreateCommand(spName);
				
				result = cmd.ExecuteReader(behavior);
				
				if (behavior == CommandBehavior.CloseConnection)
				{
					CanClose = false;				
				}
									
			}
			catch (Exception e)
			{
				if (result != null)
				{
					result.Close();
					result = null;
				}
					
				throw new DALException("Error in DALEngine::ExecSP_DataReader, in stored procedure '" + spName + "'", e);
			}		
			
			return result;		
		
		}

		public object ExecSP_Scalar(string spName)
		{
			object result = null;
			
			try
			{			
				IDbCommand cmd = CreateCommand(spName);
				
				result = cmd.ExecuteScalar();
				
				UpdateOutputParameters(cmd);
									
			}
			catch (Exception e)
			{				
				throw new DALException("Error in DALEngine::ExecSP_Scalar, in stored procedure '" + spName + "'", e);
			}		
			
			return result;		
		
		}

		public int ExecSP_NonQuery(string spName)
		{
			int result = -1;
			
			try
			{
				IDbCommand cmd = CreateCommand(spName);
				
				result = cmd.ExecuteNonQuery();
				
				UpdateOutputParameters(cmd);
									
			}
			catch (Exception e)
			{				
				throw new DALException("Error in DALEngine::ExecSP_NonQuery, in stored procedure '" + spName + "'", e);
			}		
			
			return result;				
		}



		// using strict SQL queries
		public abstract void ExecQuery_DataSet(string query, DataSet dataSet, string tableName);
		

		public IDataReader ExecQuery_DataReader(string query, CommandBehavior behavior)
		{
			IDataReader result = null;
			
			try
			{			
				IDbCommand cmd = Connection.CreateCommand();
				cmd.CommandText = query;
				result = cmd.ExecuteReader(behavior);
				
				if (behavior == CommandBehavior.CloseConnection)
				{
					CanClose = false;
				}
				
			}
			catch (Exception e)
			{
					
				throw new DALException("Error in DALEngine::ExecQuery_DataReader, in query '" + query + "'", e);
			}		
			
			return result;		
		}

		public IDataReader ExecQuery_DataReader(string query)
		{
			return ExecQuery_DataReader(query, CommandBehavior.Default);	
		}

		public object ExecQuery_Scalar(string query)
		{
			object result = null;
			
			try
			{
				IDbCommand cmd = Connection.CreateCommand();
				cmd.CommandText = query;
				result = cmd.ExecuteScalar();
									
			}
			catch (Exception e)
			{
				throw new DALException("Error in DALEngine::ExecQuery_Scalar, in query '" + query + "'", e);
			}
			
			return result;
		}

		public int ExecQuery_NonQuery(string query)	
		{
			int result = -1;
			
			try
			{
				IDbCommand cmd = Connection.CreateCommand();
				cmd.CommandText = query;
				result = cmd.ExecuteNonQuery();				
									
			}
			catch (Exception e)
			{
				throw new DALException("Error in DALEngine::ExecQuery_NonQuery, in query '" + query + "'", e);
			}			
			
			return result;
		}
	





		//
		// Business objects methods
		//
		public static object CreateFromReader(IDataReader reader, Type objType)
		{
			object instance = Activator.CreateInstance(objType, true);
			
			PropertyInfo[] properties = objType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
			
			for (int i=0; i < properties.Length; i++)
			{
				BaseFieldAttribute[] fields = (BaseFieldAttribute[])properties[i].GetCustomAttributes(typeof(BaseFieldAttribute), true);

				if (fields.Length > 0)
				{
					object value = reader[fields[0].ColumnName];
					
					if (properties[i].PropertyType.IsEnum)
					{
						value = Enum.Parse(properties[i].PropertyType, value.ToString());
					}

					if (properties[i].PropertyType == typeof(Int32) && value.ToString() == string.Empty)
						properties[i].SetValue(instance, -1, null);
					else if (properties[i].PropertyType == typeof(Int32))
						properties[i].SetValue(instance, value, null);
					
					if (properties[i].PropertyType == typeof(String) && value.ToString() == string.Empty)
						properties[i].SetValue(instance, string.Empty, null);
					else if (properties[i].PropertyType == typeof(String))
						properties[i].SetValue(instance, value, null);
				}
			}
			
			
			return instance;
		
		}

		public void DeleteObject(int keyValue, Type objType)
		{
			
			DataTableAttribute[] dataTables = (DataTableAttribute[])objType.GetCustomAttributes(typeof(DataTableAttribute), true);
			if (dataTables.Length > 0)
			{
				PropertyInfo[] properties = objType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
				string clause = "";

				for (int i = 0; i < properties.Length; i++)
				{
					KeyFieldAttribute[] keys = (KeyFieldAttribute[])properties[i].GetCustomAttributes(typeof(KeyFieldAttribute), false);

					if (keys.Length > 0)
					{
						clause = keys[0].ColumnName + " = " + keyValue;
						break;
					}
				}

				StringBuilder sb = new StringBuilder("DELETE FROM [");
				sb.Append(dataTables[0].TableName);
				sb.Append("] ");
				sb.Append("WHERE ");
				sb.Append(clause);

				ExecQuery_NonQuery(sb.ToString());
			}
		}
		
		public object RetrieveObject(object keyValue, Type objType)
		{
			object result = null;
		
			DataTableAttribute[] dataTables = (DataTableAttribute[])objType.GetCustomAttributes(typeof(DataTableAttribute), true);
			
			if (dataTables.Length > 0)
			{
			
				PropertyInfo[] properties = objType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
				string clause = "";

				for (int i=0; i < properties.Length; i++)
				{
					KeyFieldAttribute[] keys = (KeyFieldAttribute[])properties[i].GetCustomAttributes(typeof(KeyFieldAttribute), false);

					if (keys.Length > 0)
					{
						if (properties[i].PropertyType == typeof(string) || properties[i].PropertyType == typeof(char))
						{
							clause = keys[0].ColumnName + " = '" + keyValue + "'";
						}
						else
						{
							clause = keys[0].ColumnName + " = " + keyValue;
						}

						break;
					}
				}


				StringBuilder sb = new StringBuilder("SELECT ");
				for (int i=0; i < properties.Length; i++)
				{
					BaseFieldAttribute[] fields = (BaseFieldAttribute[])properties[i].GetCustomAttributes(typeof(BaseFieldAttribute), true);
					
					if (fields.Length > 0)
					{											
						sb.Append("[");
						sb.Append(fields[0].ColumnName);
						sb.Append("], ");
					}
				}
				
				// remove the last ','
				sb.Remove(sb.Length - 2, 2);				

				sb.Append(" FROM [");
				sb.Append(dataTables[0].TableName);
				sb.Append("] ");
				sb.Append("WHERE ");
				sb.Append(clause);
				
				IDataReader reader = ExecQuery_DataReader(sb.ToString());						
				
				try
				{
					if (reader.Read())
					{
						result = CreateFromReader(reader, objType);
					}
				}
				catch (Exception e)
				{
					throw new DALException("Failed to retrieve object [" + objType + "] with sql " + sb.ToString(), e);
				}
				finally
				{
					if (reader != null)
					{
						reader.Dispose();
					}
				}				
			}
			else
			{
				throw new ArgumentException("The DataTable attribute wasn't found in the object [keyValue parameter]");
			}
			
			return result;		
		}
		
		
		void UpdateObjectSql(object o, DataTableAttribute dataTable)
		{
			DALQueryBuilder qBuilder = null;
			
			
			// verifica se existe algum campo chave
			PropertyInfo[] properties = o.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
			PropertyInfo keyProperty = null;

			for (int i=0; i < properties.Length; i++)
			{
				KeyFieldAttribute[] keys = (KeyFieldAttribute[])properties[i].GetCustomAttributes(typeof(KeyFieldAttribute), false);
				
				if (keys.Length > 0)
				{
					if (properties[i].PropertyType == typeof(string) || properties[i].PropertyType == typeof(char))
					{
						string value = properties[i].GetValue(o, null).ToString();
						if (value != "")
						{
							qBuilder = new DALQueryBuilder(dataTable.TableName, "[" + keys[0].ColumnName + "] = '" + value + "'");
						}							
					}
					else
					{
						// numeric
						string value = properties[i].GetValue(o, null).ToString();
						if (value != "0")
						{
							qBuilder = new DALQueryBuilder(dataTable.TableName, "[" + keys[0].ColumnName + "] = " + value);
						}							
					}

					keyProperty = properties[i];
					
					break;
				}
			}

			if (qBuilder == null)
			{
				qBuilder  = new DALQueryBuilder(dataTable.TableName);
			}

			
			for (int i=0; i < properties.Length; i++)
			{
				BaseFieldAttribute[] fields = (BaseFieldAttribute[])properties[i].GetCustomAttributes(typeof(BaseFieldAttribute), true);
				
				if (fields.Length > 0)
				{
					if (!(fields[0] is KeyFieldAttribute))
					{				
						object value = properties[i].GetValue(o, null);
						if (value.GetType().IsEnum)
						{
							value = Convert.ToInt32(value);
						}
					
						qBuilder.Add(fields[0].ColumnName, value);
					}
				}
			}

			ExecQuery_NonQuery(qBuilder.ToString());
			
			// just retrieve if it's a numeric key. probably an auto number column.
			if (qBuilder.IsInserting && keyProperty.PropertyType != typeof(string) && keyProperty.PropertyType != typeof(char))
			{
				object newKey = ExecQuery_Scalar("SELECT @@IDENTITY AS 'Identity'");
				keyProperty.SetValue(o, Convert.ChangeType(newKey, keyProperty.PropertyType), null);
			}
			
		
		}

		
		int UpdateObjectStoredProcedure(object o, DataTableAttribute dataTable)
		{
			int returnedId = -1;
			DALParameter keyParameter = null;
			PropertyInfo keyProperty  = null;
			PropertyInfo[] properties = o.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
			
			ClearParameters();
			
			for (int i=0; i < properties.Length; i++)
			{
				BaseFieldAttribute[] fields = (BaseFieldAttribute[])properties[i].GetCustomAttributes(typeof(BaseFieldAttribute), true);

				if (fields.Length > 0)
				{
					object value  = properties[i].GetValue(o, null);
					if (value != null)
					{
						if (value.GetType().IsEnum)
						{
							value = Convert.ToInt32(value);
						}
						else if (!(fields[0] is KeyFieldAttribute) && value.ToString() == "0")
							value = DBNull.Value;
					}
					else
					{
						value = DBNull.Value;
					}
					
					DALParameter param = new DALParameter("@" + fields[0].ColumnName, value);
					
					DataFieldAttribute dataField = fields[0] as DataFieldAttribute;
					if (dataField != null)
					{
						param.Type = dataField.Type;

						if (dataField.Size != 0)
						{
							param.Size = dataField.Size;
						}
					}
					else if (fields[0] is KeyFieldAttribute)
					{
						keyProperty  = properties[i];
						keyParameter = param;
						
						param.Direction = ParameterDirection.InputOutput;
					}

					AddParameter(param);
				}
			}				

			if (keyProperty == null || keyParameter == null)
			{
				throw new ArgumentException("The object " + o + " doesn't has a KeyField attribute");
			}

			ExecSP_NonQuery(dataTable.UpdateStoredProcedure);
			returnedId = (int)keyParameter.Value;
			
			keyProperty.SetValue(o, Convert.ChangeType(keyParameter.Value, keyProperty.PropertyType), null);

			return returnedId;
		}
		
		public int UpdateObject(object o)
		{
			int returnedId = -1;
			Type type = o.GetType();
			DataTableAttribute[] dataTables = (DataTableAttribute[])type.GetCustomAttributes(typeof(DataTableAttribute), true);
			
			if (dataTables.Length > 0)
			{
				if (dataTables[0].UpdateStoredProcedure != "")
				{
					returnedId = UpdateObjectStoredProcedure(o, dataTables[0]);
				}
				else
				{
					UpdateObjectSql(o, dataTables[0]);
				}	
			}
			else
			{
				throw new ArgumentException("The DataTable attribute wasn't found in the object [o parameter]");			
			}

			return returnedId;
		}
		
		public void UpdateObjects(IEnumerable enumObjects)
		{
			foreach(object o in enumObjects)
			{
				UpdateObject(o);
			}
		}
		
		
		
		
		
		public int RetrieveChildObjects(object foreignKeyValue, List<object> objects, Type parentType, Type childType)
		{
			int result = 0;
		
			DataTableAttribute[] dataTables = (DataTableAttribute[])childType.GetCustomAttributes(typeof(DataTableAttribute), true);
			
			if (dataTables.Length > 0)
			{
			
				PropertyInfo[] properties = childType.GetProperties(BindingFlags.Public | BindingFlags.Instance);
				string clause = "";

				for (int i=0; i < properties.Length; i++)
				{
					ForeignKeyFieldAttribute[] keys = (ForeignKeyFieldAttribute[])properties[i].GetCustomAttributes(typeof(ForeignKeyFieldAttribute), false);

					if (keys.Length > 0)
					{
						if (keys[0].PKTable == parentType.Name)
						{
							if (properties[i].PropertyType == typeof(string) || properties[i].PropertyType == typeof(char))
							{
								clause = "[" + keys[0].ColumnName + "] = '" + foreignKeyValue + "'";
							}
							else
							{
								clause = "[" + keys[0].ColumnName + "] = " + foreignKeyValue;
							}

							break;
						}
					}
				}
				
				if (clause == "")
				{
					throw new ArgumentException("The object [" + childType + "] doesn't have a ForeignKey attribute");
				
				}


				StringBuilder sb = new StringBuilder("SELECT ");
				for (int i=0; i < properties.Length; i++)
				{
					BaseFieldAttribute[] fields = (BaseFieldAttribute[])properties[i].GetCustomAttributes(typeof(BaseFieldAttribute), true);

					if (fields.Length > 0)
					{
						sb.Append("[");
						sb.Append(fields[0].ColumnName);
						sb.Append("], ");
					}
				}
				
				// remove the last ','
				sb.Remove(sb.Length - 2, 2);				

				sb.Append(" FROM [");
				sb.Append(dataTables[0].TableName);
				sb.Append("] ");
				sb.Append("WHERE ");
				sb.Append(clause);
								
				
				IDataReader reader = null;
				try
				{
					reader = ExecQuery_DataReader(sb.ToString());						
					while (reader.Read())
					{
						result++;
						objects.Add(CreateFromReader(reader, childType));
					}
				}
				catch (Exception e)
				{
					throw new DALException("Failed to retrieve child objects [" + childType + "] with sql " + sb.ToString(), e);
				}
				finally
				{
					if (reader != null)
					{
						reader.Dispose();
					}
				}
			}
			else
			{
				throw new ArgumentException("The DataTable attribute wasn't found in the object [keyValue parameter]");
			}
			
			return result;
		}
	}	
}